﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace RetriveDataFromNorthwind
{
    class TaskSix
    {
        static void Main(string[] args)
        {
            SqlConnection dbCon = new SqlConnection("Server=.\\SQLEXPRESS; " +
                "Database=Northwind; Integrated Security=true");
            dbCon.Open();
            using (dbCon)
            {
                string command = "SELECT cat.CategoryName as CategoryName, prod.ProductName as ProductName FROM Categories as cat" +
                    " INNER JOIN Products as prod ON(prod.CategoryID = cat.CategoryID)";
                SqlCommand cmd = new SqlCommand(command, dbCon);
                SqlDataReader dataReader = cmd.ExecuteReader();
                string oldCategoryName = "";
                using (dataReader)
                {
                    while (dataReader.Read())
                    {
                        string productName = (string)dataReader["ProductName"];
                        string categoryName = (string)dataReader["CategoryName"];

                        if (oldCategoryName != categoryName)
                        {
                            Console.WriteLine(new string('#',20));
                            Console.WriteLine();

                            oldCategoryName = categoryName;

                            Console.WriteLine("CategoryName: {0}",oldCategoryName);
                            Console.WriteLine(new string('-', 20));
                        }

                        Console.WriteLine("ProductName: {0}", productName);
                    }
                }
            }
        }
    }
}
